package com.cola.dao;

import com.cola.entity.Area;
import com.cola.entity.City;
import com.cola.utils.DBControl;
import com.cola.utils.DateUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Max
 * @since 17/09/06
 */
public class CommonDao extends BaseDao {
    private static volatile CommonDao instance = null;
    private final Logger              logger   = LoggerFactory.getLogger(getClass());

    /**
     * 获取地区列表
     * @return List<Area>
     * @author Max
     * @since 17/09/06
     */
    public List<Area> getAreaList() {
        Connection conn = null;

        try {
            conn = DBControl.getConnection();

            String sql = "select * from system_area where status=1 order by 'order'";

            return getQueryRunner().query(conn, sql, new BeanListHandler<Area>(Area.class));
        } catch (Exception e) {
            logger.error("获取区域列表异常！");
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return null;
    }

    /**
     * 获取城市列表
     * @return List<City>
     * @author Max
     * @since 17/09/06
     */
    public List<City> getCityList() {
        Connection conn = null;

        try {
            conn = DBControl.getConnection();

            String sql = "select * from system_area where status=1 and parent_id=0 order by 'order'";

            return getQueryRunner().query(conn, sql, new BeanListHandler<City>(City.class));
        } catch (Exception e) {
            logger.error("获取区域列表异常！");
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return null;
    }

    /**
     * 根据类型获取字典列表
     * @param String type;
     * @return List<Map<String,Object>>
     * @author Max
     * @since 17/09/06
     */
    public List<Map<String,Object>> getCommonContentByType(String type) {
        Connection        conn = null;
        try {
            conn = DBControl.getConnection();

            String sql = "select * from system_dictionary where type =? and status =1 order by `order`";

            return getQueryRunner().query(conn,sql,new MapListHandler(),type);
        } catch (Exception e) {
            logger.error("获取字典表内容异常!", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return null;
    }

    /**
     * 添加导入操作日志
     * @param map 导入记录信息
     * @return true:成功；false:失败
     * @author Max
     * @since 2017/9/6
     */
    public boolean addImportLog(Long userId,String type,String fileName,String fileUrl,String username,Integer count,Integer successCount,Integer failCount,Integer importStatus,String desc){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "insert into cola_import_log(user_id,type,file_name,file_url,username,count,success_count,fail_count," +
                    "import_time,import_status,status,`desc`) values(?,?,?,?,?,?,?,?,?,?,1,?)";
            return getQueryRunner().update(conn,sql,userId,type,fileName,fileUrl,username,count,
                    successCount,failCount, DateUtils.getCurrentDateTime(),importStatus,desc)>0;
        }catch (Exception e){
            logger.error("添加导入操作日志异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }


    /**
     * 获取字典列表
     * @return List<Map<String，Object>>
     * @author Max
     * @since 17/09/06
     */
    public List<Map<String,Object>> getDictionaryList(String type) {
        Connection conn = null;

        try {
            conn = DBControl.getConnection();

            String sql = "select * from system_dictionary where status=1 and type=? order by 'order'";

            return getQueryRunner().query(conn, sql, new MapListHandler(),type);
        } catch (Exception e) {
            logger.error("获取字典列表！");
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 获取区域编号与城市编号的对应关系
     * @return Map<String,String>
     * @author Max
     * @since 2017/9/11
     */
    public Map<String,String> getAreaToCity(){
        Map<String,String> resultMap = new HashMap<String,String>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.area_no,b.area_no as city_no FROM `system_area` a INNER JOIN system_area b " +
                    "on a.parent_id = b.area_id where a.parent_id>0 and a.status =1;";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                resultMap.put(rs.getString(1),rs.getString(2));
            }
            return resultMap;
        }catch (Exception e){
            logger.error("",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * @return CommonDao
     * @author Max
     * @since 17/09/06
     */
    public static CommonDao getInstance() {
        if (instance == null) {
            synchronized (CommonDao.class) {
                if (instance == null) {
                    instance = new CommonDao();
                }
            }
        }

        return instance;
    }
}
